﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using NHibernate;

namespace Biza.Core.Orm
{
    public class SqlHelper
    {
        public IList ExecuteQueryList(string strSql)
        {
            using (var ctx = UnitOfWork.Start())
            {

                var list = ctx.Session.CreateSQLQuery(strSql).List();
                return list;
            }
        }
        public IList<T> ExecuteQueryList<T>(string strSql)
        {
            using (var ctx = UnitOfWork.Start())
            {
                var list = ctx.Session.CreateSQLQuery(strSql).AddEntity(typeof(T)).List<T>();

                return list;
            }
        }

        public object ExecuteScalar(string strSql)
        {
            using (var ctx = UnitOfWork.Start())
            {
                var cmd = GetSqlCmd(ctx.Session.Connection, strSql);
                var obj = cmd.ExecuteScalar();

                return obj;
            }
        }
        public DataTable ExecuteQuery(string strSql)
        {
            using (var ctx = UnitOfWork.Start())
            {
                var dt = new DataTable("newTable");
                var cmd = GetSqlCmd(ctx.Session.Connection, strSql);
                dt.Load(cmd.ExecuteReader());

                return dt;
            }
        }
        public int ExecuteNoQuery(string strSql, params IDbDataParameter[] Params)
        {
            using (var ctx = UnitOfWork.Start())
            {
                var cmd = GetSqlCmd(ctx.Session.Connection, strSql);
                cmd = SetCmdParamters(cmd, Params);
                var obj = cmd.ExecuteNonQuery();
                return obj;
            }
        }

        public object ExecuteProcScalar(string procName, params IDbDataParameter[] Params)
        {
            using (var ctx = UnitOfWork.Start())
            {
                var cmd = GetProcCmd(ctx.Session.Connection, procName);
                cmd = SetCmdParamters(cmd, Params);
                var obj = cmd.ExecuteScalar();
                return obj;
            }
        }
        public int ExecuteProcNonQuery(string procName, params IDbDataParameter[] Params)
        {
            using (var ctx = UnitOfWork.Start())
            {

                var cmd = GetProcCmd(ctx.Session.Connection, procName);
                cmd = SetCmdParamters(cmd, Params);
                var obj = cmd.ExecuteNonQuery();
                return obj;
            }

        }
        public DataTable ExecuteProcReader(string procName, params IDbDataParameter[] Params)
        {
            using (var ctx = UnitOfWork.Start())
            {

                var cmd = GetProcCmd(ctx.Session.Connection, procName);
                cmd = SetCmdParamters(cmd, Params);
                var reader = cmd.ExecuteReader();
                var dt = new DataTable("table");
                dt.Load(reader);
                return dt;
            }
        }


        private IDbCommand GetSqlCmd(IDbConnection conn, string strSql)
        {
            return GetCommand(conn, strSql, CommandType.Text);
        }
        private IDbCommand GetProcCmd(IDbConnection conn, string procName)
        {
            return GetCommand(conn, procName, CommandType.StoredProcedure);
        }
        private IDbCommand GetCommand(IDbConnection conn, string cmdText, CommandType cmdType)
        {
            IDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            return cmd;
        }
        private IDbCommand SetCmdParamters(IDbCommand cmd, params IDbDataParameter[] parameters)
        {
            if (parameters == null) return cmd;
            foreach (var v in parameters) cmd.Parameters.Add(v);

            return cmd;
        }
    }
}
